import pymysql
from python_baway.common.xdb import xdb_args
from python_baway.common.xcommon import sep, xshow_table


class MysqlHelper(object):

    def __init__(self, host, database, user, password, port=3306, charset='utf8'):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset
        self.__conn = None
        self.__cursor = None

    def __del__(self):
        print('Destructor: MysqlHelper')
        self.close()

    def connect(self):
        if self.__conn is not None:
            return
        self.__conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            passwd=self.password,
            database=self.database,
            charset=self.charset
        )
        self.__cursor = self.__conn.cursor()

    def close(self):
        if self.__conn is None:
            return
        self.__cursor.close()
        self.__conn.close()
        self.__conn = None
        self.__cursor = None
        print('Connection closed.')

    def query(self, sql):
        self.connect()
        sep(sql)
        self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    def exec(self, sql):
        self.connect()
        sep(sql)
        r = self.__cursor.execute(sql)
        self.__conn.commit()
        return r


if '__main__' == __name__:
    helper = MysqlHelper(**xdb_args)
    rows = helper.query("SELECT * FROM `users` LIMIT 5")
    xshow_table(rows)
    print(helper.exec("UPDATE `users` SET password='Ilyt' WHERE id < 4"))
    rows = helper.query("SELECT * FROM `users` LIMIT 5")
    xshow_table(rows)
